<?php
declare (strict_types = 1);

namespace app\admin\controller;

use app\BaseController;
use app\model\Collect;
use app\model\OrderDetail;
use app\model\Sale as SaleModel;
use app\model\Users;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Sale extends BaseController
{
    public function index()
    {
        $keyword=request()->param('keyword');
        $time=request()->param('time');

        $status=[1=>'sales|desc',2=>'sales|asc',3=>'browse|desc',4=>'browse|asc'];

        $data=Db::table('product')->alias('p')
            ->leftJoin('order_detail od','od.product_id=p.id')
            ->field('p.name, p.type,p.id, p.num,p.banners,p.price,MAX(od.created_at) as latest_created_at, p.browse, od.product_id, SUM(od.price * od.num) as sale_money, SUM(od.num) as sales')
            ->group('p.id')
            ->when($time,function ($query) use($time){
                $start=strtotime(explode(',',$time)[0]);
                $end=strtotime(explode(',',$time)[1]);
                $query->whereBetween('od.created_at',[$start,$end]);
            })->when($keyword,function($query) use($keyword,$status){
                foreach ($status as $k=>$v){
                    if($k==$keyword){
                        $query->order(explode('|',$v)[0],explode('|',$v)[1]);
                    }
                }
            })
            ->order('latest_created_at', 'desc')->paginate(10)

            ->each(function($item, $key){

                if($item['type']==1){
                    $item['num']='-';
                }
                $item['created_at'] = date('Y-m-d');
                $item['collect']=Collect::where('products_id',$item['id'])->count();
                return $item;
            });;


        return $this->success($data);
    }

    public function excel()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $sheet->setCellValue('A1', '產品名');
        $sheet->setCellValue('B1', '價格');
        $sheet->setCellValue('C1', '瀏覽量');
        $sheet->setCellValue('D1', '銷量');
        $sheet->setCellValue('E1', '銷售額');
        $sheet->setCellValue('F1', '庫存');

        //设置宽度
        $sheet->getColumnDimension('A')->setWidth(20);

        // 设置表格数据
        $data=Db::table('product')->alias('p')
            ->leftJoin('order_detail od','od.product_id=p.id')
            ->field('p.name, p.type,p.id, p.num,p.banners,p.price,MAX(od.created_at) as latest_created_at, p.browse, od.product_id, SUM(od.price * od.num) as sale_money, SUM(od.num) as sales')
            ->group('p.id')

            ->order('sale_money', 'desc')->select()->toArray();
        $row = 2;

        foreach ($data as $item) {

            $sheet->setCellValue('A' . $row, $item['name']);
            $sheet->setCellValue('B' . $row, $item['price']);
            $sheet->setCellValue('C' . $row, $item['browse']);
            $sheet->setCellValue('D' . $row, $item['sales'] ? $item['sales'] :0);
            $sheet->setCellValue('E' . $row, $item['sale_money'] ? $item['sale_money']:0);
            $sheet->setCellValue('F' . $row, $item['num']);
            $row++;
        }
        // 输出 Excel 文件
//        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        header('Content-Disposition: attachment;filename="demo.xlsx"');
//        header('Cache-Control: max-age=0');
//        $writer->save('php://output');
//        exit;
        $filename = './uploads/數據分析.xlsx';
        if (!is_dir('uploads')) {
            mkdir('uploads', 0777, true);
        }
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save($filename);
        return $this->success(ossUpload($filename,'exel','xlsx')) ;
    }

    public function delete($id)
    {
        SaleModel::where('id',$id)->delete();
        return $this->success();
    }

    public function round()
    {
        $keyword=request()->param('keyword');
        $time=request()->param('time');
        $status=[1=>'sales|desc',2=>'sale_money|desc',3=>'browse|desc',4=>'num|desc'];

        $data=Db::table('product')->alias('p')
            ->leftJoin('order_detail od','od.product_id=p.id')
            // ->field('p.name,p.id,p.num,p.browse,od.product_id,od.price as sale_money od.num as sales')
            ->field('p.name, p.id, p.num, p.browse, od.product_id, SUM(od.price * od.num) as sale_money, SUM(od.num) as sales')
            ->group('p.id')
            ->when($time,function ($query) use($time){
                $start=strtotime(explode(',',$time)[0]);
                $end=strtotime(explode(',',$time)[1]);
                $query->whereBetween('od.created_at',[$start,$end]);
            })->when($keyword,function($query) use($keyword,$status){
                foreach ($status as $k=>$v){
                    if($k==$keyword){
                        $query->order(explode('|',$v)[0],explode('|',$v)[1]);
                    }
                }
            })
            ->order('sale_money','desc')->limit(5)->select()->toArray();

        foreach ($data as &$item){

            $item['number']=$item['sale_money'];
            if(!empty($keyword) && $keyword==1){
                $item['number']=$item['sales'];
            }
            if(!empty($keyword) && $keyword==2){

                $item['number']=$item['sale_money'];
            }
            if(!empty($keyword) && $keyword==3){
                $item['number']=$item['browse'];
            }
            if(!empty($keyword) && $keyword==4){
                $item['number']=$item['num'];
            }
        }

        return $this->success($data);
    }

    public function getmember()
    {
        $keyword=request()->param('keyword');
        $time=request()->param('time');
        $data=Users::with('grade')
            ->when($time,function ($query) use($time){
                $start=strtotime(explode(',',$time)[0]);
                $end=strtotime(explode(',',$time)[1]);
                $query->whereBetween('created_at',[$start,$end]);
            })->when($keyword,function ($query) use($keyword){
                if($keyword==1){
                    $query->order('consume','desc');
                }
                if($keyword==2) {
                    $query->order('consume_num', 'desc');
                }
                if($keyword==3){
                    $query->order('users_integral_id', 'desc');
                }
            })
            ->limit(5)
            ->order('consume','desc')
            ->field('username,consume,id,users_integral_id,consume_num')
            ->select();

        foreach ($data as &$item){
            $item->number=$item->consume;
            if(!empty($keyword) && $keyword==1){
                $item->number=$item->consume;
            }
            if(!empty($keyword) && $keyword==2){
                $item->number=$item->consume_num;
            }
            if(!empty($keyword) && $keyword==3){
                $item->number=$item->grade->integral;
            }
        }

        return $this->success($data);
    }

    /**
     * 圖形分析下載
     * @return void
     */
    public function dowpdf()
    {
        $keyword=request()->param('sale');
        $status=[1=>'sales|desc',2=>'sale_money|desc',3=>'browse|desc',4=>'num|desc'];
        $data= \app\model\Product::when($keyword,function($query) use($keyword,$status){
            foreach ($status as $k=>$v){
                if($k==$keyword){
                    $query->order(explode('|',$v)[0],explode('|',$v)[1]);
                }
            }
        })

            ->field('name,sales,id,sale_money,num,browse,price')
            ->order('id','asc')->limit(5)->select()->toArray();
//        $data=\app\model\Product::order('id','asc')->limit(5)->select()->toArray();
        $pdf = new \TCPDF('P', 'mm', 'A4', true, 'UTF-8', false);

        $pdf->SetCreator('My Application');
        $pdf->SetAuthor('John Doe');
        $pdf->SetTitle('My PDF');
        $pdf->SetSubject('Test');

        // Set header

        $pdf->SetHeaderData('', 0, '數據分析', '');
        $pdf->setHeaderFont(['stsongstdlight', '', '10']);
        // Add a page
        $pdf->AddPage();

        // Write some content
//        $pdf->SetFont('stsongstdlight', '', 12);
//        $pdf->Write(5, "訂單號：$data->order_no");
//        $pdf->Ln(); // Add a line break
//        $pdf->Write(5, "價格：$data->pay_price");
//        $pdf->Ln(); // Add a line break
//        $pdf->Write(5,"下單時間：$data->created_at");
//        $pdf->Ln(); // Add a line break
//        $pdf->Write(5,"支付方式：{$type[$data->pay_mode]}");
//        $pdf->SetY(50);
        $pdf->SetFont('stsongstdlight', 'B', 12);
        $pdf->Cell(100, 10, '產品名稱', 1, 0, 'C');
        $pdf->Cell(30, 10, '價格', 1, 0, 'C');
        $pdf->Cell(30, 10, '銷售額', 1, 0, 'C');
        $pdf->Ln();


// Add table rows
        $pdf->SetFont('stsongstdlight', '', 12);
        foreach($data as $detail) {

            $pdf->Cell(100, 10, $detail['name'], 1, 0, 'L');
            $pdf->Cell(30, 10, $detail['price'], 1, 0, 'C');
            $pdf->Cell(30, 10, $detail['sales'], 1, 0, 'R');
            $pdf->Ln();
        }
        $pdf->Ln(); // Add a line break
        $pdf->Write(5, '如果您有任何問題，請使用下列聯絡資訊：於 APP/ Facebook 專頁 聯絡我們！感謝您的惠顧！');
        $pdf->Ln(); // Add a line break
        $pdf->Ln();
        $pdf->Write(5, 'DATE：' . date('Y-m-d:H:i:s'));
        // Output the PDF as a string
//        $pdfData = $pdf->Output('電子發票.pdf', 'S');
//        $this->success(ossUpload($pdfData));
        // Return the PDF data to the browser
        $name= runtime_path(). '/pdf';

        $fileName= '數據分析' . '.pdf';
        $pdf->Output($name.$fileName, 'F');
        return $this->success(ossUpload($name.$fileName),'pdf','pdf');
    }

    public function dowexc()
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $sheet->setCellValue('A1', '產品名');
        $sheet->setCellValue('B1', '價格');
        $sheet->setCellValue('C1', '瀏覽量');
        $sheet->setCellValue('D1', '銷量');
        $sheet->setCellValue('E1', '銷售額');
        $sheet->setCellValue('F1', '庫存');

        //设置宽度
        $sheet->getColumnDimension('A')->setWidth(20);
        $keyword=request()->param('sale');
        $status=[1=>'sales|desc',2=>'sale_money|desc',3=>'browse|desc',4=>'num|desc'];
        $data= \app\model\Product::when($keyword,function($query) use($keyword,$status){
            foreach ($status as $k=>$v){
                if($k==$keyword){
                    $query->order(explode('|',$v)[0],explode('|',$v)[1]);
                }
            }
        })

            ->field('name,sales,id,sale_money,num,browse,price')
            ->order('id','asc')->limit(5)->select()->toArray();
        // 设置表格数据
//        $data=\app\model\Product::order('id','asc')->limit(5)->select()->toArray();
        $row = 2;

        foreach ($data as $item) {

            $sheet->setCellValue('A' . $row, $item['name']);
            $sheet->setCellValue('B' . $row, $item['price']);
            $sheet->setCellValue('C' . $row, $item['browse']);
            $sheet->setCellValue('D' . $row, $item['sales']);
            $sheet->setCellValue('E' . $row, $item['sale_money']);
            $sheet->setCellValue('F' . $row, $item['num']);
            $row++;
        }

        $filename = './uploads/數據分析.xlsx';
        if (!is_dir('uploads')) {
            mkdir('uploads', 0777, true);
        }
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save($filename);
        return $this->success(ossUpload($filename,'exel','xlsx')) ;
    }
}
